﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
   public class EmailDAL
    {
        SqlConnection str = new SqlConnection("Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;pwd=123456");
        /// <summary>
        /// 加入
        /// </summary>
        /// <returns></returns>
        public List<Email> ShowEmail()
        {
            str.Open();
            string sql = "select * from Email";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Email> listEmail = new List<Email>();
            foreach (DataRow item in ta.Rows)
            {
                Email l = new Email();
                l.Email1 = item["Email1"].ToString();
                l.EmailCode = item["EmailCode"].ToString();
                l.EmailContent = item["EmailContent"].ToString();
                l.EmailId = Convert.ToInt32(item["EmailId"]);
                l.EmailName = item["EmailName"].ToString();
                listEmail.Add(l);
            }
            return listEmail;

        }
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteEmail(int id)
        {
            str.Open();
            string sql = "delete Email where EmailId='" + id + "'";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 添加信息
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        public int Email_Add(Model.Email p)
        {
            str.Open();
            string sql = "insert into Email values('" + p.Email1 + "','" + p.EmailCode + "'.'"+p.EmailContent+"','"+p.EmailName+"')";
            SqlCommand com = new SqlCommand(sql, str);
            var i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 显示详细信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Email ShowEmailX(int id)
        {
            str.Open();
            string sql = "select * from Project where EmailId='" + id + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            str.Close();
            List<Email> listEmail = new List<Email>();
            foreach (DataRow item in ta.Rows)
            {
                Email l = new Email();
                l.Email1 = item["Email1"].ToString();
                l.EmailCode = item["EmailCode"].ToString();
                l.EmailContent = item["EmailContent"].ToString();
                l.EmailId = Convert.ToInt32(item["EmailId"]);
                l.EmailName = item["EmailName"].ToString();
                listEmail.Add(l);
            }
            return listEmail.FirstOrDefault();
        }
        /// <summary>
        /// 修改信息
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public int UpdateEmail(Email data)
        {
            str.Open();
            string sql = "update Email set  EmailName='" + data.EmailName + "',Email1='" + data.Email1 + "',EmailCode='" + data.EmailCode + "',EmailContent='" + data.EmailContent + "' where EmailId='" + data.EmailId + "'";
            SqlCommand com = new SqlCommand(sql, str);
            int i = com.ExecuteNonQuery();
            return i;
        }
        /// <summary>
        /// 搜索
        /// </summary>
        /// <param name="pp"></param>
        /// <returns></returns>
        public List<Email> SeleteEmail(string name)
        {
            str.Open();
            string sql = "selete Email1,EmailCode,EmailContent,EmailId,EmailName from Email where EmailName like '" + name + "'";
            SqlDataAdapter adap = new SqlDataAdapter(sql, str);
            DataTable ta = new DataTable();
            adap.Fill(ta);
            List<Email> listEmail = new List<Email>();
            foreach (DataRow item in ta.Rows)
            {
                Email l = new Email();
                l.Email1 = item["Email1"].ToString();
                l.EmailCode = item["EmailCode"].ToString();
                l.EmailContent = item["EmailContent"].ToString();
                l.EmailId = Convert.ToInt32(item["EmailId"]);
                l.EmailName = item["EmailName"].ToString();
                listEmail.Add(l);
            }
            return listEmail;
        }
    }
}
